﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
#pragma warning disable 436

namespace Code
{
    public class HoaDonDataAccess
    {
        protected DataProvider _provider = DataProvider.GlobalDataProvider;

        public bool DeleteHoaDon(String strID)
        {
            string strSQL = string.Format("Delete From CHI_TIET_HOA_DON " +
                                          "Where idHoaDon = {0} " +
                                          "Delete From HOA_DON " +
                                          "Where id = {0} ",strID);

            if (_provider.ExecuteNonQuery(strSQL) > 0)
                return true;
            else
                return false;
        }

        public bool UpdateHoaDon(HoaDonInfo hd)
        {
            string strSQL = "";
            if(hd.idNguoiXacNhan != -1)
                   strSQL = string.Format("Update HOA_DON " +
                                          "Set idPTTT = {0}, idPTGH = {1}, TongTien = {2}, NguoiXacNhan = {3}, " + 
                                          "NgayXacNhan = '{4}', GhiChu = N'{5}', TenNguoiNhan = N'{6}', " + 
                                          "DiaChiNguoiNhan = N'{7}', SDTNguoiNhan = '{8}' " + "Where id = {9}", 
                                          hd.idPhuongThucThanhToan, hd.idPhuongThucGiaoHang, hd.TongTien, hd.idNguoiXacNhan, hd.NgayXacNhan, hd.GhiChu, hd.TenNguoiNhan, hd.DiaChi, hd.SoDienThoai, hd.MaHoaDon);
            else
                strSQL = string.Format("Update HOA_DON " +
                                       "Set idPTTT = {0}, idPTGH = {1}, TongTien = {2}, NguoiXacNhan = {3}, " +
                                       "NgayXacNhan = {4}, GhiChu = N'{5}', TenNguoiNhan = N'{6}', " +
                                       "DiaChiNguoiNhan = N'{7}', SDTNguoiNhan = '{8}' " + "Where id = {9}",
                                        hd.idPhuongThucThanhToan, hd.idPhuongThucGiaoHang, hd.TongTien, "NULL", "NULL", hd.GhiChu, hd.TenNguoiNhan, hd.DiaChi, hd.SoDienThoai, hd.MaHoaDon);

            if (_provider.ExecuteNonQuery(strSQL) > 0)
                return true;
            else
                return false;
        }

        public DataTable GetListSanPham(String strID)
        {
            string strSQL = string.Format("Select cthd.id as 'ID',  sp.TenSanPham as 'TenSanPham',ha.URL as 'HinhAnh', cthd.SoLuong as 'SoLuong' " +
                                          "From CHI_TIET_HOA_DON cthd, SAN_PHAM sp, HINH_ANH ha " +
                                          "Where cthd.idSanPham = sp.Id and cthd.idHinhAnh = ha.id and cthd.idHoaDon = {0}",strID);
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public DataTable GetHoaDonDaXacNhan(String strID)
        {
            string strSQL = string.Format("Select  ndh.Ten as 'NguoiDatHang'," +
                                          "NgayDatHang as 'NgayDatHang', PT_THANH_TOAN.TenPhuongThuc as 'PhuongThucThanhToan'," +
                                          "PT_GIAO_HANG.TenPhuongThuc as 'PhuongThucGiaoHang'," +
                                          "TongTien as 'TongTien',nxn.TenDangNhap as 'NguoiXacNhan', NgayXacNhan as 'NgayXacNhan'," +
                                          "GhiChu, TenNguoiNhan, SDTNguoiNhan as 'SoDienThoai', DiaChiNguoiNhan as 'DiaChi'" +
                                          "From HOA_DON, NGUOI_DUNG ndh, NGUOI_DUNG nxn, PT_THANH_TOAN, PT_GIAO_HANG " +
                                          "Where ndh.id = NguoiDatHang and nxn.id = NguoiXacNhan and PT_THANH_TOAN.id = idPTTT and PT_GIAO_HANG.id = idPTGH and HOA_DON.id = {0}", strID);

            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public DataTable GetHoaDonChuaXacNhan(String strID)
        {
            string strSQL = string.Format("Select  ndh.Ten as 'NguoiDatHang', " +
                                          "NgayDatHang as 'NgayDatHang', PT_THANH_TOAN.TenPhuongThuc as 'PhuongThucThanhToan', " +
                                          "PT_GIAO_HANG.TenPhuongThuc as 'PhuongThucGiaoHang', " +
                                          "TongTien as 'TongTien',GhiChu, TenNguoiNhan, SDTNguoiNhan as 'SoDienThoai', DiaChiNguoiNhan as 'DiaChi' " +
                                          "From HOA_DON, NGUOI_DUNG ndh, PT_THANH_TOAN, PT_GIAO_HANG " +
                                          "Where HOA_DON.NguoiXacNhan is NULL and ndh.id = NguoiDatHang and PT_THANH_TOAN.id = idPTTT and PT_GIAO_HANG.id = idPTGH and HOA_DON.id = {0}", strID);

            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public DataTable GetListPhuongThucThanhToan()
        {
            string strSQL = string.Format("Select * From PT_THANH_TOAN");
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public DataTable GetListPhuongThucGiaoHang()
        {
            string strSQL = string.Format("Select * From PT_GIAO_HANG");
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public DataTable GetListNhanVien()
        {
            string strSQL = string.Format("Select id,TenDangNhap From NGUOI_DUNG Where idLoaiNguoiDung = 2");
            return _provider.ExecuteQueryDataTable(strSQL);
        }
    }
}
